const conn = require('../db/config')

exports.all = (req,res) => {
    var sql = 'select * from goods'
    var page = req.query.offset || 0
    var limit = req.query.limit || 10
    conn.query(sql,(err,data)=>{
        if(err){
            return res.send('错误')
        }
        var sqllen = 'select g.num,g.name,c.name as category,g.price,g.sum,g.details from goods as g left join category as c on cate_id = id order by g.num LIMIT '+ limit +' offset '+ page 
        conn.query(sqllen,(err,result)=>{
            if(result.length)
                res.send({
                    //查询到的总数据长度返回给分页
                    total:data.length,
                    status: 200,
                    data:result
                })
            else
                res.send({
                    msg:"暂无数据",
                    status: 500
                })
        })
    })
}

exports.search = (req,res) => {
    //num||name||category
    var type = req.query.searchtype
    var index = req.query.indexdata
    console.log(type,index)
    if(type!=='category'){
        if(type=='name')  index = "'"+index+"'"
        var sql = 'select * from goods where '+type+' = '+index
        console.log('我执行到此')
    }
    else{
        index = "'"+index+"'"
        var sql = 'select g.num,g.name,c.name as category,g.price,g.sum,g.details from goods as g left join category as c on g.cate_id=c.id where c.name ='+index
    }
    conn.query(sql,(err,result) => {
        if(err)  return res.send('错误')
        if(result.length) res.send({data:result, status: 200})
        else  res.send({mag:'暂无数据', status:500})
    })
}

exports.addnew = (req,res) => {
    var gooddata = "('"+req.query.num+"','"+req.query.name+"','"+req.query.cate_id+"','"+req.query.price+"','"+req.query.sum+"','"+req.query.details+"')"
    //判断编号和商品名是否存在于数据库，是则返回警告信息
    conn.query('select * from goods where num ="'+req.query.num+'"',(err,row) => {
        if(err)  return console.log(err)
        if(row.length) return res.send({msg:'该编号已存在对应数据请勿重复添加！',status:400})
    })
    conn.query('select * from goods where name ="'+req.query.name+'"',(err,row) => {
        if(err)  return console.log(err)
        if(row.length) return res.send({msg:'该商品名已存在请勿重复添加！',status:400})
    })
    conn.query('insert INTO goods (num,name,cate_id,price,sum,details) VALUES '+gooddata,(err,result) => {
        if(err)  return res.send({msg:'添加商品失败，数据库出错',status:500})
        else return res.send({msg:'商品添加成功！',status:200})
    })
}

exports.updateold = (req,res) => {
    //这bug我修不好了就这吧
    var sql = 'select * from goods where num= '+req.query.num+' and name= "'+req.query.name+'" and price = "'+req.query.price+'"and sum = "'+req.query.sum+'" and details = "'+req.query.details+'"'
    conn.query(sql,(err,row) => {
        if(err)  return console.log(err)
        console.log(row)
        if(row.length) return res.send({msg:'您未修改任何数据!',status:400})
        else {
            var sqllen = 'UPDATE goods SET name= "'+req.query.name+'",price = "'+req.query.price+'",sum = "'+req.query.sum+'" and details = "'+req.query.details+'" WHERE num = '+req.query.num
            conn.query(sqllen,(err,result) =>{
                if(err)  return console.log({msg:'更新失败，数据库出错',status:500})
                else res.send({msg:'更新成功！', status:200})
            })
        }
    })
}

//单行删除和多行删除的功能有待实现
exports.singledel = (req,res) => {
    //接收当前行内的数据
    var num = req.query.num
    conn.query('DELETE FROM `goods` WHERE num = '+num,(err,result) => {
        if(err)    return res.send({msg:'删除失败，数据库出错',status:500}) 
        else    return res.send({msg:'已删除！',status:200}) 
    })
}

exports.delanylines = (req,res) => {
    //期望返回结果是[1,3,2,6]
    //DELETE FROM `city` WHERE ID in ("[18,19,21,22]")可以正常执行
    var numgroup = req.query.numgroup
    conn.query('DELETE FROM `goods` WHERE num in ("'+numgroup+'")',(err,result) => {
        if(err)    return res.send({msg:'删除失败，数据库出错',status:500}) 
        else    return res.send({msg:'已删除！',status:200}) 
    })
}